let express = require("express");
const fs = require('fs');
var conn = require('../public/javascripts/connect');
var parseExcel = require('../public/download/parseExcel');//生成excel
var department = require('../public/download/department');//表头数据
var calc = require('../public/Backstage/Calculation');//3级巴的计算
var calcZB = require('../public/Backstage/ZBCalculationSummary');//2级巴装备的计算
var zhuangbeiSummary = require('../public/Backstage/zhuangbeiSummary');//2级巴装备的汇总
let FZ = require('../public/TwoGradeBar/FZ');//2级巴发展的汇总
let FZCAL = require('../public/TwoGradeBar/FZCAL');//2级巴发展的计算
let ZX = require('../public/TwoGradeBar/ZX');//2级巴中心的汇总
let ZXCAL = require('../public/TwoGradeBar/ZXCAL');//2级巴中心的汇总
let JSWL = require('../public/OneGradeBar/JSWL');//1级巴金石物流的计算
let JSWLCAL = require('../public/OneGradeBar/JSWLCAL');//1级巴金石物流的计算let
let ZONGBU = require('../public/TwoGradeBar/ZONGBU');//2级巴总部管理费用汇总
let ZONGBUCAL = require('../public/TwoGradeBar/ZONGBUCAL');//2级巴总部管理费用计算
let QITA = require('../public/TwoGradeBar/Other');//2级巴总部管理费用汇总
let QITACAL = require('../public/TwoGradeBar/OtherCal');//2级巴总部管理费用计算
let router = express.Router();
router.all('*', function (req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "X-Requested-With");
    res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS");
    res.header("X-Powered-By", ' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});
router.post('/', async function (req, res, next) {
    console.log("req", req)
    console.log("req.body", req.body)
    let obj = req.body;
    obj = JSON.parse(Object.keys(obj));
    console.log("objobjobj", obj);
    let tabYM = `${obj.tablename}_${obj.year}_${obj.month}`
    if (obj.tablename.indexOf('经营管理中心') != -1) {
        let tabYM1 = `仓储分公司_${obj.year = Number(obj.month) <= 1 ? obj.year - 1 : obj.year}_${Number(obj.month) - 1 > 1 ? Number(obj.month) - 1 : 12}`;
        let sql11 = `INSERT INTO ${tabYM}(行号,科目) SELECT 行号,科目 FROM ${tabYM1}`;
        let sql12 = `UPDATE ${tabYM} SET 修改日期=now() WHERE 行号=124`;
        await new Promise((res, rej) => {
            conn.connection2.query(sql11, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
        await new Promise((res, rej) => {
            conn.connection2.query(sql12, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
    }
    let sql = `INSERT INTO ${obj.tablename}_${obj.year}_${obj.month}(行号,科目,${obj.col},年份,月份,修改日期)VALUES (${obj.row},'${obj.subject}',${obj.value},${obj.year},${obj.month},now() ) ON DUPLICATE KEY UPDATE 行号 = ${obj.row},${obj.col} = ${obj.value}`;
    console.log("sql", sql);
    conn.connection2.query(sql, function (err, result, files) {
        if (err) {
            console.log('[ERROR]', err.message);
            return;
        }
        console.log("result", result);
        res.json({"success": true})
    })
});

async function download(obj) {
    let {tablename, year, month, grade} = obj;
    let year1, month1, sql, arr, arrFileds;
    let objData = {}, departname = `${tablename}_${year}_${month}`, lineNum = 0,
        premonthdepartname = `${tablename}_${month > 1 ? year : year - 1}_${month >= 1 ? month - 1 : 12}`;
    console.log("grade", grade);
    if (month > 1) {
        sql = `SELECT A.*,B.去年同期累计 B1,B.当月累计金额 B2 FROM  ${departname} A LEFT JOIN  ${premonthdepartname} B on A.行号=B.行号`;
    } else {
        sql = `SELECT A.*,0 B1,0 B2 FROM  ${departname} A`;
    }
    arr = ["e", "f", "k", "r", "s", "v", "x", "y", "ai", "i", "m"];
    arrFileds = ["去年同期抓取", "去年同期调整", "年度计划金额", "上月抓取值", "上月调整值", "当月计划值", "当月抓取值", "当月调整值", "下月计划值", "B1", "B2"];
    await new Promise((resP, rej) => {
        conn.connection2.query(sql, function (err, result, fileds) {
            if (err) {
                console.log('[SELECT ERROR]::', err);
                return;
            }
            for (let i in result) {
                lineNum = result[i]["行号"]
                for (let j = 0; j < arrFileds.length; j++) {
                    lineNum1 = arr[j] + lineNum;
                    objData[lineNum1] = result[i][arrFileds[j]]
                }
            }
            // console.log("objData", objData);
            resP(true)
        })
    })
    console.log("department.gongsiName(tablename)", department.gongsiName(tablename));
    let gonsiName = await department.gongsiName(tablename);
    console.log("公司名字", gonsiName);
    console.log("objData", objData);
    console.log("下载excel表时取的数据", sql);
    await department.renderBiaotou1(objData, gonsiName, year, month, gonsiName[2]).then(async data => {
        await parseExcel({
            src: "/home/HX/www/FirstApp/static/template.xlsx",
            dest: `gFilke/out/${tablename}_${year}_${month}`,
            json: data
        });
    });
    // 计算完毕开始下载end
}//下载的方法
router.post('/end', async function (req, res, next) {//3级巴全部结束发送end开始计算
    console.log("req.body", req.body)
    let obj = req.body;
    // obj = JSON.parse(Object.keys(obj));
    console.log("objobjobj", obj);
    // res.json({kjj:55})
    let fieldarr = ["去年同期累计", "年度计划金额", "当月累计金额"];//需要从上个月获取的数据(对应获取)
    //需要从上个月获取的数据(不对应获取)
    let tabYM = `${obj.tablename}_${obj.year}_${obj.month}`;
    let tabYM1 = `${obj.tablename}_${Number(obj.month) <= 1 ? Number(obj.year) - 1 : obj.year}_${Number(obj.month) <= 1 ? 12 : Number(obj.month) - 1}`;
    let fieldarr2 = [];
    fieldarr2[0] = `CALL disRep('${tabYM}','${tabYM1}','上月抓取值','当月抓取值')`;
    fieldarr2[1] = `CALL disRep('${tabYM}','${tabYM1}','上月调整值','当月调整值')`;
    fieldarr2[2] = `CALL disRep('${tabYM}','${tabYM1}','上月实际值','当月实际值')`;
    fieldarr2[3] = `CALL disRep('${tabYM}','${tabYM1}','当月计划值','下月计划值')`;
    if (obj.end) {
        let upsql
        //需要从上个月获取的数据(对应获取)
        for (let i = 0; i < fieldarr.length; i++) {
            if (obj.month <= 1) {
                //如果小与等于1月份,累计值等于0;
                upsql = `UPDATE ${obj.tablename}_${obj.year}_${obj.month} a,${obj.tablename}_${obj.month <= 1 ? obj.year - 1 : obj.year}_${obj.month == 1 ? 12 : obj.month - 1} b SET a.${fieldarr[i]}=0 WHERE a.行号=b.行号`;
            } else {
                //如果大与等于1月份,累计值等于上个月的累计值;
                upsql = `UPDATE ${obj.tablename}_${obj.year}_${obj.month} a,${obj.tablename}_${obj.year}_${obj.month == 1 ? 12 : obj.month - 1} b SET a.${fieldarr[i]}=b.${fieldarr[i]} WHERE a.行号=b.行号`;
            }
            await (async function (i) {
                console.log("upsql", upsql);
                await new Promise(resP => {
                    conn.connection2.query(upsql, function (err, result, files) {
                        if (err) {
                            console.log('[ERROR UPDATE]', err.message);
                            return;
                        }
                        console.log("result", result);
                        resP(true)
                    })
                })
            })(i)
        }
        // 需要从上个月获取的数据(不对应获取)
        for (let j = 0; j < fieldarr2.length; j++) {
            await (async function (j) {
                await new Promise(resP => {
                    conn.connection2.query(fieldarr2[j], function (err, result, files) {
                        if (err) {
                            console.log('[ERROR UPDATE]', err.message);
                            return;
                        }
                        console.log("result", result);
                        resP(true)
                    })
                })
            })(j)
        }
        await calc.calculation1.cal(tabYM);//执行计算

        //延迟执行抓取完成的状态begin
        // time1();

        async function time1() {
            //如果可以汇总在任务队列中插入一条记录(汇总完成插入用时多久)begin
            let insertSql = `UPDATE 任务列表 SET 当前状态='抓取完成',结束时间=NOW() WHERE 部门名称 = '${obj.tablename}_${obj.year}_${obj.month}'`
            let insertSql2 = `UPDATE 任务列表 SET 总共用时=(SELECT D.C FROM (SELECT TIMESTAMPDIFF(MINUTE,B.开始时间,B.结束时间) C FROM 任务列表 B WHERE 部门名称 = '${obj.tablename}_${obj.year}_${obj.month}') D)  WHERE 部门名称 = '${obj.tablename}_${obj.year}_${obj.month}'`
            console.log("insertSql", insertSql);
            console.log("insertSql2发顺丰", insertSql2);
            await new Promise(resP => {
                conn.connection4.query(insertSql, function (err, result, files) {
                    if (err) {
                        console.error("插入汇总结束时间状态出错", err);
                        return;
                    }
                    resP(true)
                })
            })
            await new Promise(resP => {
                conn.connection4.query(insertSql2, function (err, result, files) {
                    if (err) {
                        console.error("插入汇总结束时间状态出错", err);
                        return;
                    }
                    resP(true)
                })
            })
            //如果可以汇总在任务队列中插入一条记录(汇总完成插入用时多久)end
            //改变抓取的状态begin
            let sqlState = `UPDATE correspondence_table SET state=0 WHERE table_name='${obj.tablename}_${obj.year}_${obj.month}'`;
            conn.connection2.query(sqlState, function (err, result, files) {
                if (err) {
                    console.log('[UPDATE ERROR] - ', err.message);
                    return;
                }
                console.log('UPDATE ID:', result);
            });
            //改变抓取的状态end
        };


        // 计算完毕开始下载begin
        await download(obj);
        // 计算完毕开始下载end
        fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
        res.json({"success": true});
    }
});
//单个单元格抓起结束进行计算
router.post('/grabend', async function (req, res, next) {
    let obj = req.body;
    // obj = JSON.parse(Object.keys(obj));
    console.log("objobjobj", obj);
    let tabYM = `${obj.tablename}_${obj.year}_${obj.month}`;
    await calc.calculation1.cal(tabYM);//执行计算
    await download(obj);
    fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
    res.json({"success": true});
});
//二级巴汇总并且在汇总完进行计算
router.post("/Summary1", async function (req, res, next) {//汇总需要的数据开始进行月份数据的整合
    let obj = req.body;
    console.log("刚刚传进来的值", obj);
    // obj = JSON.parse(Object.keys(obj));
    let {end, tablename, year, month} = obj;
    let tabYM = `${obj.tablename}_${obj.year}_${obj.month}`
    console.log("objobjobj", tablename);
    console.log("tabYM", tabYM);

    //汇总完成改变抓取状态begin
    async function time1() {
        //如果可以汇总在任务队列中插入一条记录(汇总完成插入用时多久)begin
        let insertSql = `UPDATE 任务列表 SET 当前状态='抓取完成',结束时间=NOW() WHERE 部门名称 = '${obj.tablename}_${obj.year}_${obj.month}'`
        let insertSql2 = `UPDATE 任务列表 SET 总共用时=(SELECT D.C FROM (SELECT TIMESTAMPDIFF(MINUTE,B.开始时间,B.结束时间) C FROM 任务列表 B WHERE 部门名称 = '${obj.tablename}_${obj.year}_${obj.month}') D)  WHERE 部门名称 = '${obj.tablename}_${obj.year}_${obj.month}'`
        console.log("insertSql", insertSql);
        console.log("insertSql2发顺丰", insertSql2);
        await new Promise(resP => {
            conn.connection4.query(insertSql, function (err, result, files) {
                if (err) {
                    console.error("插入汇总结束时间状态出错", err);
                    return;
                }
                resP(true)
            })
        })
        await new Promise(resP => {
            conn.connection4.query(insertSql2, function (err, result, files) {
                if (err) {
                    console.error("插入汇总结束时间状态出错", err);
                    return;
                }
                resP(true)
            })
        })
        //如果可以汇总在任务队列中插入一条记录(汇总完成插入用时多久)end
        //改变抓取的状态begin
        let sqlState = `UPDATE correspondence_table SET state=0 WHERE table_name='${obj.tablename}_${obj.year}_${obj.month}'`;
        conn.connection2.query(sqlState, function (err, result, files) {
            if (err) {
                console.log('[UPDATE ERROR] - ', err.message);
                return;
            }
            console.log('UPDATE ID:', result);
        });
        //改变抓取的状态end
    };
    //汇总完成改变抓取状态end
    if (tablename == "物流装备事业部") {//为二级巴插入142行行数和科目
        let tabYM1 = `物流装备事业部_${Number(obj.month) <= 1 ? obj.year - 1 : obj.year}_${Number(obj.month) > 1 ? Number(obj.month) - 1 : 12}`;
        let sql = `INSERT INTO ${tabYM}(行号,科目) SELECT 行号,科目 FROM ${tabYM1}`
        await new Promise((res, rej) => {
            conn.connection2.query(sql, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
        await zhuangbeiSummary.calculation1.cal(tabYM);//2级巴装备汇总
        await calcZB.calculation1.cal(tabYM);//2级巴装备计算
        // time1();
        await download(obj);
        fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
        res.json({"success": true});
    } else if (tablename == "物流发展事业部") {
        let tabYM1 = `物流发展事业部_${Number(obj.month) <= 1 ? obj.year - 1 : obj.year}_${Number(obj.month) > 1 ? Number(obj.month) - 1 : 12}`;
        let sql = `INSERT INTO ${tabYM}(行号,科目) SELECT 行号,科目 FROM ${tabYM1}`
        await new Promise((res, rej) => {
            conn.connection2.query(sql, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
        await FZ.calculation1.cal(tabYM);//2级巴发展汇总
        await FZCAL.calculation1.cal(tabYM);//2级巴发展计算
        // time1();
        await download(obj);
        fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
        res.json({"success": true});
    } else if (tablename == "经营管理中心") {
        let tabYM1 = `经营管理中心_${Number(obj.month) <= 1 ? obj.year - 1 : obj.year}_${Number(obj.month) > 1 ? Number(obj.month) - 1 : 12}`;
        let sql = `INSERT INTO ${tabYM}(行号,科目) SELECT 行号,科目 FROM ${tabYM1}`;
        await new Promise((res, rej) => {
            conn.connection2.query(sql, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
        await ZX.calculation1.cal(tabYM);//2级巴中心汇总
        await ZXCAL.calculation1.cal(tabYM);//2级巴中心计算
        // time1();
        await download(obj);
        fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
        res.json({"success": true});
    } else if (tablename == "金石物流") {
        let tabYM1 = `金石物流_${Number(obj.month) <= 1 ? obj.year - 1 : obj.year}_${Number(obj.month) > 1 ? Number(obj.month) - 1 : 12}`;
        let sql = `INSERT INTO ${tabYM}(行号,科目) SELECT 行号,科目 FROM ${tabYM1}`;
        await new Promise((res, rej) => {
            conn.connection2.query(sql, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
        await JSWL.calculation1.cal(tabYM);//1级巴金石物流汇总
        await JSWLCAL.calculation1.cal(tabYM);//1级巴金石物流计算
        // time1();
        await download(obj);
        fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
        res.json({"success": true});
    } else if (tablename == "总部费用") {
        let tabYM1 = `总部费用_${Number(obj.month) <= 1 ? obj.year - 1 : obj.year}_${Number(obj.month) > 1 ? Number(obj.month) - 1 : 12}`;
        let sql = `INSERT INTO ${tabYM}(行号,科目) SELECT 行号,科目 FROM ${tabYM1}`;
        await new Promise((res, rej) => {
            conn.connection2.query(sql, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
        await ZONGBU.calculation1.cal(tabYM);//2级巴总部管理费用汇总
        await ZONGBUCAL.calculation1.cal(tabYM);//2级巴总部管理费用计算
        // time1();
        await download(obj);
        fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
        console.log("刚刚传进来的值", obj);
        res.json({"success": true});
    } else if (tablename == "其它") {
        let tabYM1 = `化工_${Number(obj.month) <= 1 ? obj.year - 1 : obj.year}_${Number(obj.month) > 1 ? Number(obj.month) - 1 : 12}`;
        let sql = `INSERT INTO ${tabYM}(行号,科目) SELECT 行号,科目 FROM ${tabYM1}`;
        await new Promise((res, rej) => {
            conn.connection2.query(sql, function (err, result, fileds) {
                if (err) {
                    console.log('[已经存在相同的行号]', err)
                    res(true)
                    return;
                }
                res(true)
                return;
            })
        })
        await QITA.calculation1.cal(tabYM);//2级巴其它
        await QITACAL.calculation1.cal(tabYM);//2级巴其它
        // time1();
        await download(obj);
        fs.chmodSync(`/home/HX/www/FirstApp/gFilke/out/${obj.tablename}_${obj.year}_${obj.month}.xlsx`, '777');
        res.json({"success": true});
    }
});
module.exports = router;